'\" t
.\"     Title: pg_standby
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/>
.\"      Date: 2021
.\"    Manual: PostgreSQL 13.3 Documentation
.\"    Source: PostgreSQL 13.3
.\"  Language: English
.\"
.TH "PG_STANDBY" "1" "2021" "PostgreSQL 13.3" "PostgreSQL 13.3 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
pg_standby \- supports the creation of a PostgreSQL warm standby server
.SH "SYNOPSIS"
.HP \w'\fBpg_standby\fR\ 'u
\fBpg_standby\fR [\fIoption\fR...] \fIarchivelocation\fR \fInextwalfile\fR \fIwalfilepath\fR [\fIrestartwalfile\fR]
.SH "DESCRIPTION"
.PP
pg_standby
supports creation of a
\(lqwarm standby\(rq
database server\&. It is designed to be a production\-ready program, as well as a customizable template should you require specific modifications\&.
.PP
pg_standby
is designed to be a waiting
\fIrestore_command\fR, which is needed to turn a standard archive recovery into a warm standby operation\&. Other configuration is required as well, all of which is described in the main server manual (see
Section\ \&26.2)\&.
.PP
To configure a standby server to use
pg_standby, put this into its
postgresql\&.conf
configuration file:
.sp
.if n \{\
.RS 4
.\}
.nf
restore_command = \*(Aqpg_standby \fIarchiveDir\fR %f %p %r\*(Aq
.fi
.if n \{\
.RE
.\}
.sp
where
\fIarchiveDir\fR
is the directory from which WAL segment files should be restored\&.
.PP
If
\fIrestartwalfile\fR
is specified, normally by using the
%r
macro, then all WAL files logically preceding this file will be removed from
\fIarchivelocation\fR\&. This minimizes the number of files that need to be retained, while preserving crash\-restart capability\&. Use of this parameter is appropriate if the
\fIarchivelocation\fR
is a transient staging area for this particular standby server, but
\fInot\fR
when the
\fIarchivelocation\fR
is intended as a long\-term WAL archive area\&.
.PP
pg_standby
assumes that
\fIarchivelocation\fR
is a directory readable by the server\-owning user\&. If
\fIrestartwalfile\fR
(or
\-k) is specified, the
\fIarchivelocation\fR
directory must be writable too\&.
.PP
There are two ways to fail over to a
\(lqwarm standby\(rq
database server when the master server fails:
.PP
Smart Failover
.RS 4
In smart failover, the server is brought up after applying all WAL files available in the archive\&. This results in zero data loss, even if the standby server has fallen behind, but if there is a lot of unapplied WAL it can be a long time before the standby server becomes ready\&. To trigger a smart failover, create a trigger file containing the word
smart, or just create it and leave it empty\&.
.RE
.PP
Fast Failover
.RS 4
In fast failover, the server is brought up immediately\&. Any WAL files in the archive that have not yet been applied will be ignored, and all transactions in those files are lost\&. To trigger a fast failover, create a trigger file and write the word
fast
into it\&.
pg_standby
can also be configured to execute a fast failover automatically if no new WAL file appears within a defined interval\&.
.RE
.SH "OPTIONS"
.PP
pg_standby
accepts the following command\-line arguments:
.PP
\fB\-c\fR
.RS 4
Use
cp
or
copy
command to restore WAL files from archive\&. This is the only supported behavior so this option is useless\&.
.RE
.PP
\fB\-d\fR
.RS 4
Print lots of debug logging output on
stderr\&.
.RE
.PP
\fB\-k\fR
.RS 4
Remove files from
\fIarchivelocation\fR
so that no more than this many WAL files before the current one are kept in the archive\&. Zero (the default) means not to remove any files from
\fIarchivelocation\fR\&. This parameter will be silently ignored if
\fIrestartwalfile\fR
is specified, since that specification method is more accurate in determining the correct archive cut\-off point\&. Use of this parameter is
\fIdeprecated\fR
as of
PostgreSQL
8\&.3; it is safer and more efficient to specify a
\fIrestartwalfile\fR
parameter\&. A too small setting could result in removal of files that are still needed for a restart of the standby server, while a too large setting wastes archive space\&.
.RE
.PP
\fB\-r\fR \fImaxretries\fR
.RS 4
Set the maximum number of times to retry the copy command if it fails (default 3)\&. After each failure, we wait for
\fIsleeptime\fR
*
\fInum_retries\fR
so that the wait time increases progressively\&. So by default, we will wait 5 secs, 10 secs, then 15 secs before reporting the failure back to the standby server\&. This will be interpreted as end of recovery and the standby will come up fully as a result\&.
.RE
.PP
\fB\-s\fR \fIsleeptime\fR
.RS 4
Set the number of seconds (up to 60, default 5) to sleep between tests to see if the WAL file to be restored is available in the archive yet\&. The default setting is not necessarily recommended; consult
Section\ \&26.2
for discussion\&.
.RE
.PP
\fB\-t\fR \fItriggerfile\fR
.RS 4
Specify a trigger file whose presence should cause failover\&. It is recommended that you use a structured file name to avoid confusion as to which server is being triggered when multiple servers exist on the same system; for example
/tmp/pgsql\&.trigger\&.5432\&.
.RE
.PP
\fB\-V\fR
.br
\fB\-\-version\fR
.RS 4
Print the
pg_standby
version and exit\&.
.RE
.PP
\fB\-w\fR \fImaxwaittime\fR
.RS 4
Set the maximum number of seconds to wait for the next WAL file, after which a fast failover will be performed\&. A setting of zero (the default) means wait forever\&. The default setting is not necessarily recommended; consult
Section\ \&26.2
for discussion\&.
.RE
.PP
\fB\-?\fR
.br
\fB\-\-help\fR
.RS 4
Show help about
pg_standby
command line arguments, and exit\&.
.RE
.SH "NOTES"
.PP
pg_standby
is designed to work with
PostgreSQL
8\&.2 and later\&.
.PP
PostgreSQL
8\&.3 provides the
%r
macro, which is designed to let
pg_standby
know the last file it needs to keep\&. With
PostgreSQL
8\&.2, the
\-k
option must be used if archive cleanup is required\&. This option remains available in 8\&.3, but its use is deprecated\&.
.PP
PostgreSQL
8\&.4 provides the
\fIrecovery_end_command\fR
option\&. Without this option a leftover trigger file can be hazardous\&.
.PP
pg_standby
is written in C and has an easy\-to\-modify source code, with specifically designated sections to modify for your own needs
.SH "EXAMPLES"
.PP
On Linux or Unix systems, you might use:
.sp
.if n \{\
.RS 4
.\}
.nf
archive_command = \*(Aqcp %p \&.\&.\&./archive/%f\*(Aq

restore_command = \*(Aqpg_standby \-d \-s 2 \-t /tmp/pgsql\&.trigger\&.5442 \&.\&.\&./archive %f %p %r 2>>standby\&.log\*(Aq

recovery_end_command = \*(Aqrm \-f /tmp/pgsql\&.trigger\&.5442\*(Aq
.fi
.if n \{\
.RE
.\}
.sp
where the archive directory is physically located on the standby server, so that the
\fIarchive_command\fR
is accessing it across NFS, but the files are local to the standby (enabling use of
ln)\&. This will:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
produce debugging output in
standby\&.log
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
sleep for 2 seconds between checks for next WAL file availability
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
stop waiting only when a trigger file called
/tmp/pgsql\&.trigger\&.5442
appears, and perform failover according to its content
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
remove the trigger file when recovery ends
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
remove no\-longer\-needed files from the archive directory
.RE
.PP
On Windows, you might use:
.sp
.if n \{\
.RS 4
.\}
.nf
archive_command = \*(Aqcopy %p \&.\&.\&.\e\earchive\e\e%f\*(Aq

restore_command = \*(Aqpg_standby \-d \-s 5 \-t C:\epgsql\&.trigger\&.5442 \&.\&.\&.\earchive %f %p %r 2>>standby\&.log\*(Aq

recovery_end_command = \*(Aqdel C:\epgsql\&.trigger\&.5442\*(Aq
.fi
.if n \{\
.RE
.\}
.sp
Note that backslashes need to be doubled in the
\fIarchive_command\fR, but
\fInot\fR
in the
\fIrestore_command\fR
or
\fIrecovery_end_command\fR\&. This will:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
use the
copy
command to restore WAL files from archive
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
produce debugging output in
standby\&.log
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
sleep for 5 seconds between checks for next WAL file availability
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
stop waiting only when a trigger file called
C:\epgsql\&.trigger\&.5442
appears, and perform failover according to its content
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
remove the trigger file when recovery ends
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
remove no\-longer\-needed files from the archive directory
.RE
.PP
The
copy
command on Windows sets the final file size before the file is completely copied, which would ordinarily confuse
pg_standby\&. Therefore
pg_standby
waits
\fIsleeptime\fR
seconds once it sees the proper file size\&. GNUWin32\*(Aqs
cp
sets the file size only after the file copy is complete\&.
.PP
Since the Windows example uses
copy
at both ends, either or both servers might be accessing the archive directory across the network\&.
.SH "AUTHOR"
.PP
Simon Riggs
<simon@2ndquadrant\&.com>
.SH "SEE ALSO"
\fBpg_archivecleanup\fR(1)
